hive 导出数据之一列多行，转为一行多列

需求：提取数据
说明：原数据是一列多行，需要转化为一行多列

待查询表为：temp_05

hive> desc temp_05;
OK
data_dt                 string
zhanghao                string
booking_time_final      string
sign                    string
hname                   string
channel                 string
min_price_weight        string
Time taken: 0.04 seconds, Fetched: 7 row(s)

待查询数据为：

hive>
    > select
    >   booking_time_final,data_dt,min_price_weight
    > from temp_05 where hname like'%兆龙%' and booking_time_final like '%2018-02%' and channel='5'
    > ;

待查询数据如图：

需要提取的数据表头如下：

预定日期	昨日价格	前天价格
2018-02-01
2018-02-02
2018-02-03
2018-02-04

可用提数 SQL

select
  booking_time_final,
  concat_ws('',collect_set(if(data_dt='20180129',min_price_weight,''))) a,
  concat_ws('',collect_set(if(data_dt='20180130',min_price_weight,''))) a2
from temp_05
where hname like'%兆龙%' and booking_time_final like '%2018-02%' and channel='5' 
group by booking_time_final ;

数据如图：

---

以下为尝试过程

hive>
    > select
    >   if(data_dt='20180129' and booking_time_final='2018-02-01',min_price_weight,'') a,
    >   if(data_dt='20180130' and booking_time_final='2018-02-01',min_price_weight,'') a2
    > from temp_05
    > where hname like'%兆龙%' and channel='5'
    > ;
OK

数据如图：

hive>
    > select
    >     data_dt,booking_time_final,
    >   if(data_dt='20180129' and booking_time_final='2018-02-01',min_price_weight,'') a,
    >   if(data_dt='20180130' and booking_time_final='2018-02-01',min_price_weight,'') a2
    > from temp_05
    > where hname like'%兆龙%' and channel='5'
    > ;

数据如图：

hive> select
    >   booking_time_final,
    >   if(data_dt='20180129',min_price_weight,'-') a,
    >   if(data_dt='20180130',min_price_weight,'-') a2
    > from temp_05
    > where hname like'%兆龙%' and booking_time_final like '%2018-02%' and channel='5'
    > ;

数据如图：

hive>
    > select
    >   booking_time_final,
    >   collect_set(if(data_dt='20180129',min_price_weight,'-')) a,
    >   collect_set(if(data_dt='20180130',min_price_weight,'-')) a2
    > from temp_05
    > where hname like'%兆龙%' and booking_time_final like '%2018-02%' and channel='5'
    > group by booking_time_final
    > ;

数据如图：